﻿using Abp.Collections.Extensions;
using Abp.Dependency;
using Abp.Extensions;
using Mt.Site.Application.Dto;
using Mt.Site.Application.Net.MimeTypes;
using Mt.Site.Core;
using Mt.Site.Core.Storage;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace Mt.Site.Application.DataExporting.Excel.NPOI
{
    public abstract class NpoiExcelExporterBase : MtSiteServiceBase, ITransientDependency
    {
        private readonly ITempFileCacheManager _tempFileCacheManager;

        protected NpoiExcelExporterBase(ITempFileCacheManager tempFileCacheManager)
        {
            _tempFileCacheManager = tempFileCacheManager;
        }

        protected FileDto CreateExcelPackage(string fileName, Action<XSSFWorkbook> creator)
        {
            var file = new FileDto(fileName, MimeTypeNames.ApplicationVndOpenxmlformatsOfficedocumentSpreadsheetmlSheet);
            var workbook = new XSSFWorkbook();
            
            creator(workbook);
            
            Save(workbook, file);
            
            return file;
        }

        protected void AddHeader(ISheet sheet, params string[] headerTexts)
        {
            AddHeader(sheet, 0, headerTexts);
        }

        protected void AddHeader(ISheet sheet,int rowIndex, params string[] headerTexts)
        {
            if (headerTexts.IsNullOrEmpty())
            {
                return;
            }

            sheet.CreateRow(rowIndex);

            for (var i = 0; i < headerTexts.Length; i++)
            {
                AddHeaderForCell(sheet, rowIndex, i, headerTexts[i]);
            }
        }

        protected void AddHeaderForCell(ISheet sheet, int rowIndex, int columnIndex, string headerText)
        {
            var cell = sheet.GetRow(rowIndex).CreateCell(columnIndex);
            cell.SetCellValue(headerText);
            var cellStyle = sheet.Workbook.CreateCellStyle();

            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            //cellStyle.FillBackgroundColor = IndexedColors.Grey50Percent.Index;
            //cellStyle.FillForegroundColor = IndexedColors.Grey50Percent.Index;

            var font = sheet.Workbook.CreateFont();
            font.IsBold = true;
            font.FontHeightInPoints = 11;
            font.FontName = "宋体";
            font.IsBold = true;

            cellStyle.SetFont(font);
            cell.CellStyle = cellStyle;
        }

        protected void AddObject<T>(ISheet sheet,T item, Action<ISheet,T> action)
        {
            action(sheet, item);
        }

        protected void AddObjects<T>(ISheet sheet, int startRowIndex, IList<T> items, params Func<T, object>[] propertySelectors)
        {
            AddObjects<T>(sheet, startRowIndex, items, (c,i) => { },i => null, propertySelectors);
        }


        protected void AddObjects<T>(ISheet sheet, int startRowIndex, IList<T> items,
            Action<ICellStyle,int> cellStyleAlter,
            Func<int,string> getFormatStr,
            params Func<T, object>[] propertySelectors)
        {
            if (items.IsNullOrEmpty() || propertySelectors.IsNullOrEmpty())
            {
                return;
            }

            Dictionary<int, ICellStyle> styles = new Dictionary<int, ICellStyle>();

            for (var i = 0; i < items.Count; i++)
            {
                var row = sheet.CreateRow(startRowIndex + i - 1);

                for (var j = 0; j < propertySelectors.Length; j++)
                {
                    var cell = row.CreateCell(j);
                    var value = propertySelectors[j](items[i]);
                    if (value != null)
                    {
                        ICellStyle dateStyle;
                        if (styles.ContainsKey(j))
                        {
                            dateStyle = styles[j];
                        }
                        else
                        {
                            var str = getFormatStr(j);
                            dateStyle = GetDefaultCellStyle(value, str, sheet.Workbook);
                            cellStyleAlter(dateStyle, j);
                            styles[j] = dateStyle;
                        }
                        SetValue(cell, value);
                        cell.CellStyle = dateStyle;
                    }
                }
            }
        }

        //避免可空类型
        protected void SetValue(ICell cell, object val)
        {
            if (val is bool)
            {
                var data = val.ConvertTo<bool>();
                cell.SetCellValue(data);
            }
            else if (val is DateTime)
            {
                var data = val.ConvertTo<DateTime>();
                cell.SetCellValue(data);
            }
            else if (val is double ||
             val is float ||
             val is long ||
             val is int ||
             val is short ||
             val is byte ||
             val is decimal)
            {
                var data = val.ConvertTo<double>();
                cell.SetCellValue(data);
            }
            else
            {
                var data = val.ConvertTo<string>();
                cell.SetCellValue(data);
            }
        }

        protected ICellStyle GetDefaultCellStyle(object val, string formatStr, IWorkbook workbook)
        {
            ICellStyle cellStyle;
            if (val is bool)
            {
                cellStyle = GetCellStyle(workbook, formatStr ?? "默认");
            }
            else if (val is DateTime)
            {
                var data = val.ConvertTo<DateTime>();
                if (data.Hour == 0 && data.Minute == 0 && data.Second == 0)
                {
                    cellStyle = GetCellStyle(workbook, formatStr ?? "时间");
                }
                else
                {
                    cellStyle = GetCellStyle(workbook, formatStr ?? "时间2");
                }
            }
            else if (val is double ||
             val is float ||
             val is long ||
             val is int ||
             val is short ||
             val is byte ||
             val is decimal)
            {
                //var data = val.ConvertTo<double>();
                if ((val + "").IndexOf(".") > 0)
                {
                    cellStyle = GetCellStyle(workbook, formatStr ?? "数字");
                }
                else
                {
                    cellStyle = GetCellStyle(workbook, formatStr ?? "数字2");
                }
            }
            else
            {
                cellStyle = GetCellStyle(workbook, formatStr ?? "默认");
            }

            return cellStyle;
        }

        /// <summary>
        /// 获取默认样式
        /// </summary>
        /// <param name="wb">IWorkbook</param>
        /// <param name="str">标识</param>
        /// <returns></returns>
        private ICellStyle GetCellStyle(IWorkbook wb, string str)
        {
            ICellStyle cellStyle = wb.CreateCellStyle();
            //IFont font = wb.CreateFont();

            //字体
            //font.FontName = "微软雅黑";

            //居中对齐        
            cellStyle.Alignment = HorizontalAlignment.Center;
            //垂直对齐
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            //自动换行
            cellStyle.WrapText = true;

            //缩进
            cellStyle.Indention = 0;
            IDataFormat datastyle = wb.CreateDataFormat();
            switch (str)
            {
                /*
                  HSSFDataFormat.GetBuiltinFormat("0.00") 与 datastyle.GetFormat("yyyy/mm/dd"):
                  前者调用的是Excel的内嵌格式
                */
                case "时间":
                    cellStyle.DataFormat = datastyle.GetFormat("yyyy-mm-dd");
                    //cellStyle.SetFont(font);
                    break;
                case "时间2":
                    cellStyle.DataFormat = datastyle.GetFormat("yyyy-mm-dd hh:mm:ss");
                    //cellStyle.SetFont(font);
                    break;
                case "数字":
                    cellStyle.DataFormat = datastyle.GetFormat("0.00");
                    //cellStyle.SetFont(font);
                    break;
                case "数字2":
                    cellStyle.DataFormat = datastyle.GetFormat("0");
                    //cellStyle.SetFont(font);
                    break;
                case "钱":
                    cellStyle.DataFormat = datastyle.GetFormat("￥#,##0");
                    //cellStyle.SetFont(font);
                    break;
                case "url":
                    IFont font = wb.CreateFont();
                    font.Underline = FontUnderlineType.Single;
                    font.Color = IndexedColors.Blue.Index;
                    font.IsItalic = true;
                    cellStyle.SetFont(font);
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    break;
                case "百分比":
                    cellStyle.DataFormat = datastyle.GetFormat("0.00%");
                    //cellStyle.SetFont(font);
                    break;
                case "中文大写":
                    cellStyle.DataFormat = datastyle.GetFormat("[DbNum2][$-804]0");
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    //cellStyle.SetFont(font);
                    break;
                case "科学计数法":
                    cellStyle.DataFormat = datastyle.GetFormat("0.00E+00");
                    //cellStyle.SetFont(font);
                    break;
                case "默认":
                    //cellStyle.SetFont(font);
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    cellStyle.DataFormat = datastyle.GetFormat("@");//全部做字符串处理
                    break;
            }
            return cellStyle;
        }

        protected void Save(XSSFWorkbook excelPackage, FileDto file)
        {
            using (var stream = new MemoryStream())
            {
                excelPackage.Write(stream);
                _tempFileCacheManager.SetFile(file.FileToken, stream.ToArray());
            }
        }

        protected void AdjustColumn(ISheet sheet, int columnLen)
        {
            sheet.DefaultRowHeightInPoints = 30;

            for (var i = 0; i < columnLen; i++)
            {
                sheet.AutoSizeColumn(i);
                var len = 50 * 256;

                if (sheet.GetColumnWidth(i) > len)
                {
                    sheet.SetColumnWidth(i, len);
                }
                else if (sheet.GetColumnWidth(i) <= 10 * 256)
                {
                    sheet.SetColumnWidth(i, 256 * 10);
                }
            }
        }

        protected void AddDataTable(ISheet sheet, int startRowIndex, DataTable dt)
        {
            Dictionary<int, ICellStyle> styles = new Dictionary<int, ICellStyle>();


            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var row = sheet.CreateRow(startRowIndex + i - 1);
                var t_row = dt.Rows[i];
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row.CreateCell(j);
                    var value = t_row[dt.Columns[j].ColumnName];
                    if (value != null)
                    {
                        ICellStyle dateStyle;
                        if (styles.ContainsKey(j))
                        {
                            dateStyle = styles[j];
                        }
                        else
                        {
                            //var str = getFormatStr(j);
                            dateStyle = GetDefaultCellStyle(value, null, sheet.Workbook);
                            //cellStyleAlter(dateStyle, j);
                            styles[j] = dateStyle;
                        }
                        SetValue(cell, value);
                        cell.CellStyle = dateStyle;
                    }
                }
            }
        }
    }
}